/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Controlador;

import java.io.*;
import java.sql.*;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

/**
 *
 * @author Isaac Paliza <anndysaac@hotmail.com>
 */
public class CTRL_CargarKardex {

    private static final ConnectionJDBC connectionJDBC = new ConnectionJDBC();
    private static final Statement statement = connectionJDBC.createStatement();
    public static String Mensaje;

    public static String cargarKardex(String ubArchivo, int idTablaMov, String idKardex, String idTipoMov,
            String idDocumento) {
        try {
            String strRutaArchivo = "C:/docPrueba/" + ubArchivo;

            FileInputStream input = new FileInputStream(strRutaArchivo);
            POIFSFileSystem fs = new POIFSFileSystem(input);

            HSSFWorkbook LibroAnalizar = new HSSFWorkbook(fs);
            HSSFSheet HojaAnalizar = LibroAnalizar.getSheetAt(0);
            Row row;
            
            
            
            for (int j=0,i = 10; i <= HojaAnalizar.getLastRowNum(); i++,j++) {
                
                row = HojaAnalizar.getRow(i);
                if(row.getCell(0).getStringCellValue().isEmpty()){
                    return "OK";
                }
                String codArticuloAux = row.getCell(0).getStringCellValue();

                String sql2 = "SELECT idLibro "
                        + "FROM t_libro "
                        + "WHERE codigoArt='" + codArticuloAux+"'";
                ResultSet resultSet2 = ConnectionJDBC.executeQuery(statement, sql2);
                resultSet2.next();
                int idLibroAux = Integer.parseInt(resultSet2.getString(1));
                
//                String observacionesAux = row.getCell(1).getStringCellValue();
                int cantidadAux = (int) row.getCell(2).getNumericCellValue();
                double precioTAux = (double) row.getCell(3).getNumericCellValue();

//                String titulo = row.getCell(1).getStringCellValue();
                String sql = "INSERT INTO t_tablamov (idTablaMov, idKardex, idTipoMov, "
                        + "idLibro, idDocumento, cantidad, precioT) "
                        + "VALUES  (" + (idTablaMov + j) + ", " + idKardex + ", " + idTipoMov + ", "
                        + idLibroAux + ", " + idDocumento + ", " + cantidadAux + ", " + precioTAux 
                        + ")";

                statement.execute(sql);
            }
            input.close();
        } catch (SQLException ex) {
            return "Sentencia SQL";
        } catch (IOException ioe) {
            return "Error diretorio local";
        }
        return "11";
    }
}